﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using sqlservice.bean;
using sqlservice.util;
using System.Data;

namespace sqlservice.service
{
    class GradeService
    {

        /// <summary>
        /// 链接数据库
        /// </summary>
        /// <returns></returns>
        public String connectServer() {
            String ret  = DBHelper.connect();
            return ret;
        }
        /// <summary>
        /// 关闭数据库
        /// </summary>
        /// <returns></returns>
        public String closeServer() {
            String ret = DBHelper.close();
            return ret;
        }
        /// <summary>
        /// 查询数据
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public List<Grade> selectGrades( String sql) {

            List<Grade> grades = new List<Grade>();
            DataSet ds = DBHelper.query(sql);
            if (ds.Tables.Count != 0)
            {
                DataTable dt = ds.Tables[0];

                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    Grade grade = new Grade();
                    String name = dt.Rows[i][1].ToString();
                    String number = dt.Rows[i][2].ToString();
                    String chinese = dt.Rows[i][3].ToString();
                    String english = dt.Rows[i][4].ToString();
                    String math = dt.Rows[i][5].ToString();

                    grade.Name = name;
                    grade.Number = number;
                    grade.Chinese = chinese;
                    grade.English = english;
                    grade.Math = math;

                    grades.Add(grade);

                }
            }

            return grades;
        }

        /// <summary>
        /// 插入数据
        /// </summary>
        /// <param name="sql"></param>
        public String insertGrade(String sql) {
            String ret;
            try
            {
                DBHelper.query(sql);

                ret = "插入数据成功！";

            }
            catch (Exception e)
            {

                ret = e.Message;
            }
            return ret;
        }

        /// <summary>
        /// 删除数据
        /// </summary>
        /// <param name="sql"></param>
        public String deleteGrade(String sql) {
            String ret;
            try
            {
                DBHelper.query(sql);

                ret = "删除数据成功！";

            }
            catch (Exception e)
            {

                ret = e.Message;
            }
            return ret;
        }
        /// <summary>
        /// 修改数据
        /// </summary>
        /// <param name="sql"></param>
        public String updateGrade(String sql) {
            String ret;
            try
            {
                DBHelper.query(sql);

                ret = "修改数据成功！";

            }
            catch (Exception e)
            {

                ret = e.Message;
            }
            return ret;
        }
    }
}
